package com.idc.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSON;

public class WriteExample {

	public static void zhixing(String filePath) {
		try {
			Workbook workbook = WorkbookFactory.create(new File(filePath));
			FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
			Sheet sheet = workbook.getSheetAt(0);
			for (Row row : sheet) {
				for (Cell cell : row) {
					if (cell.getCellType() == CellType.FORMULA) {
						// 执行计算并将结果缓存到单元格
						evaluator.evaluateFormulaCell(cell);
					}
				}
			}
			// 保存修改后的文件
			FileOutputStream fos = new FileOutputStream(filePath);
			workbook.write(fos);
			workbook.close();
			fos.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	public static void main(String[] args) throws InterruptedException {
		List<DemoData> dataList = new ArrayList<>();
		dataList.add(new DemoData(10.0, 5, ""));
		dataList.add(new DemoData(20.0, 3, ""));
		String filePath = "C:\\Users\\" + System.getProperty("user.name") + "\\Desktop\\output.xlsx";
//		EasyExcel.write(filePath, DemoData.class).registerWriteHandler(new FormulaCellWriteHandler()).sheet("Sheet1")
//				.doWrite(dataList);

//		WriteWorkbook writeWorkbook = new WriteWorkbook();
//		writeWorkbook.setCustomWorkbook(new SXSSFWorkbook()); // 启用流式写入

		ExcelWriter excelWriter = EasyExcel.write(filePath, DemoData.class)
				.registerWriteHandler(new FormulaCellWriteHandler()).build();
		// 创建Sheet对象
		WriteSheet writeSheet = EasyExcel.writerSheet("Sheet1").build();
		excelWriter.write(dataList, writeSheet);

		Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
		evaluator.evaluateAll(); // 全表公式计算

		excelWriter.finish();

		List<DemoData2> dataList2 = new ArrayList<>();
		EasyExcel
				.read(filePath, DemoData2.class, new ReadListener<DemoData2>() {
					@Override
					public void invoke(DemoData2 data, AnalysisContext context) {
						System.out.println(JSON.toJSONString(data));
						dataList2.add(data);
					}

					@Override
					public void doAfterAllAnalysed(AnalysisContext context) {

					}
				}).sheet(0).doRead();
		EasyExcel.write("C:\\Users\\" + System.getProperty("user.name") + "\\Desktop\\output2.xlsx", DemoData2.class)
				.sheet("Sheet1")
				.doWrite(dataList2);
	}
}
